This present invention relates in general to database management systems and more particularly to a system and method for extracting index key data fields from a data table.
A computer database may be characterized as a table having one or more columns of data. Each column of data may have a fixed or variable number of data characters. Each row of the table typically comprises one database record, such that the same number of columns are present in each database record. A column may also be identified as an index key, such that a first index key column is sorted, and a second index key column is then sorted for each occurrence of an entry in the first column. For example, if the first index key column is xe2x80x9cstatesxe2x80x9d and the second index key column is xe2x80x9ccities,xe2x80x9d the index will be an alphabetical listing of states in the first column and an alphabetical listing of each city in each state in the second column.
Indices are typically generated for computer databases for a variety of purposes. For example, an index may be generated to allow a user to readily determine relevant groups of data, or to readily determine whether certain classes of data contain other subclasses of data. An index may also be generated if the data storage device for a prior version of the index has been damaged. Further, an index will be generated if the database must be restored from a backed-up version of the database, so as to compare the backed up version with the last known version to ensure data continuity.
Although indices have many important uses, extraction of index key data fields from a database may require significant processing time. Known methods and systems for extracting index key data fields typically check the data stored in each column of each row of the database, even if such data is not an index key. The data may be checked to determine whether it is, for example, a date field, a decimal field, a floating point field, an integer field, a small integer field, a time stamp field, a character field, a graphic field, a variable character field, a variable graphic field, or other fields. Each of these checks requires processor time. Thus, for a database that includes 30 columns of which only three are used for indices, the processor performs at least 90 percent more processing than may be necessary to extract the index key data fields.
Therefore, a system and method for extracting index key data fields from a data table are required that substantially eliminate or reduce the problems associated with conventional systems and methods for extracting index key data fields from a data table.
In particular, a system and method for extracting index key data fields from a data table are required that do not require extensive data processing to be performed on data fields that are not index key data fields.
In accordance with one embodiment of the present invention, a system for extracting index key data fields is provided. The system includes a definition interrogator that extracts index definitions and column definitions from a database. The system also includes a code generator that is connected to the definition interrogator. The code generator builds computer code for extracting index key data fields based upon the index definitions and the column definitions. A table analyzer is connected to the code generator and uses the computer code to extract index data from a data table.
The present invention provides many advantages. One advantage of the present invention is a method and system for index key extraction that optimizes the use of processor resources for extracting index keys from table data. The present invention decreases the use of processor resources by skipping unneeded characters in a data record or table row when extracting index keys, and does not analyze the characters to determine or verify that they comprise a certain type of data.
Another advantage of the present invention is a system and method for index key extraction that increases the speed of index key extraction. The present invention extracts index and column field definitions, and uses these extracted definitions to analyze database tables in a manner that is quicker than prior art methods.